import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
import os
from datetime import datetime

class ExcelStatsGenerator:
    def __init__(self, file_path):
        self.file_path = file_path
        self.wb = None
        self.stats_sheet_name = "统计汇总"
        
    def load_workbook(self):
        """加载工作簿"""
        try:
            self.wb = load_workbook(self.file_path)
            print(f"✓ 工作簿加载成功，包含 {len(self.wb.sheetnames)} 个工作表")
            return True
        except Exception as e:
            print(f"✗ 加载工作簿失败: {e}")
            return False
    
    def create_summary_sheet(self):
        """
        创建统计汇总工作表
        统计所有工作表的行数（不包括首行）
        """
        if not self.wb:
            if not self.load_workbook():
                return False
        
        # 如果统计工作表已存在，先删除
        if self.stats_sheet_name in self.wb.sheetnames:
            del self.wb[self.stats_sheet_name]
            print(f"✓ 已删除现有的 '{self.stats_sheet_name}' 工作表")
        
        # 创建新的统计工作表
        stats_sheet = self.wb.create_sheet(title=self.stats_sheet_name)
        print(f"✓ 已创建 '{self.stats_sheet_name}' 工作表")
        
        # 设置表头
        headers = ["工作表名称", "数据行数", "总行数", "是否有表头", "统计时间"]
        
        # 设置表头样式
        header_font = Font(bold=True, color="FFFFFF", size=12)
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        center_align = Alignment(horizontal="center", vertical="center")
        border = Border(
            left=Side(style="thin"),
            right=Side(style="thin"),
            top=Side(style="thin"),
            bottom=Side(style="thin")
        )
        
        # 写入表头
        for col, header in enumerate(headers, 1):
            cell = stats_sheet.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = center_align
            cell.border = border
        
        # 统计每个工作表的数据
        total_data_rows = 0
        total_all_rows = 0
        row_index = 2
        
        for sheet_name in self.wb.sheetnames:
            if sheet_name == self.stats_sheet_name:
                continue  # 跳过统计工作表本身
                
            ws = self.wb[sheet_name]
            
            # 计算行数
            all_rows = self._get_sheet_row_count(ws)
            data_rows = max(0, all_rows - 1)  # 排除首行（表头）
            
            # 检查是否有表头（首行是否有数据）
            has_header = self._check_has_header(ws)
            
            # 写入统计信息
            stats_sheet.cell(row=row_index, column=1, value=sheet_name).border = border
            stats_sheet.cell(row=row_index, column=2, value=data_rows).border = border
            stats_sheet.cell(row=row_index, column=3, value=all_rows).border = border
            stats_sheet.cell(row=row_index, column=4, value="是" if has_header else "否").border = border
            stats_sheet.cell(row=row_index, column=5, value=datetime.now().strftime("%Y-%m-%d %H:%M:%S")).border = border
            
            total_data_rows += data_rows
            total_all_rows += all_rows
            row_index += 1
            
            print(f"  📊 {sheet_name}: 总行数={all_rows}, 数据行数={data_rows}")
        
        # 添加汇总行
        if row_index > 2:  # 如果有数据
            summary_row = row_index + 1
            
            # 汇总行样式
            summary_font = Font(bold=True, color="FFFFFF", size=11)
            summary_fill = PatternFill(start_color="C65911", end_color="C65911", fill_type="solid")
            
            # 写入汇总信息
            stats_sheet.cell(row=summary_row, column=1, value="汇总").border = border
            stats_sheet.cell(row=summary_row, column=2, value=total_data_rows).border = border
            stats_sheet.cell(row=summary_row, column=3, value=total_all_rows).border = border
            stats_sheet.cell(row=summary_row, column=4, value="-").border = border
            stats_sheet.cell(row=summary_row, column=5, value="-").border = border
            
            # 应用汇总行样式
            for col in range(1, 6):
                cell = stats_sheet.cell(row=summary_row, column=col)
                cell.font = summary_font
                cell.fill = summary_fill
                cell.alignment = center_align
        
        # 调整列宽
        self._adjust_column_width(stats_sheet)
        
        # 设置数据区域样式
        self._format_data_area(stats_sheet, row_index)
        
        print(f"\n📈 统计完成:")
        print(f"   - 统计工作表: {self.stats_sheet_name}")
        print(f"   - 总数据行数: {total_data_rows}")
        print(f"   - 总工作表数: {len(self.wb.sheetnames) - 1}")  # 排除统计表本身
        
        return True
    
    def _get_sheet_row_count(self, worksheet):
        """获取工作表的实际行数"""
        # 使用openpyxl的方法获取有数据的最大行
        return worksheet.max_row
    
    def _check_has_header(self, worksheet):
        """检查工作表是否有表头（首行是否有数据）"""
        if worksheet.max_row == 0:
            return False
        
        # 检查第一行是否有任何非空单元格
        first_row = worksheet[1]
        for cell in first_row:
            if cell.value is not None and str(cell.value).strip() != "":
                return True
        return False
    
    def _adjust_column_width(self, worksheet):
        """自动调整列宽"""
        column_widths = {}
        
        for row in worksheet.iter_rows():
            for cell in row:
                if cell.value:
                    # 计算单元格内容长度
                    length = len(str(cell.value))
                    # 更新列宽
                    if cell.column not in column_widths or length > column_widths[cell.column]:
                        column_widths[cell.column] = length
        
        # 设置列宽（增加一些边距）
        for column, width in column_widths.items():
            adjusted_width = min(max(width + 2, 8), 50)  # 限制宽度在8-50之间
            worksheet.column_dimensions[openpyxl.utils.get_column_letter(column)].width = adjusted_width
    
    def _format_data_area(self, worksheet, data_end_row):
        """格式化数据区域"""
        center_align = Alignment(horizontal="center", vertical="center")
        border = Border(
            left=Side(style="thin"),
            right=Side(style="thin"),
            top=Side(style="thin"),
            bottom=Side(style="thin")
        )
        
        # 设置数据区域对齐方式和边框
        for row in worksheet.iter_rows(min_row=2, max_row=data_end_row):
            for cell in row:
                cell.alignment = center_align
                cell.border = border
        
        # 设置数字列的格式
        for row in range(2, data_end_row + 1):
            for col in [2, 3]:  # 数据行数和总行数列
                cell = worksheet.cell(row=row, column=col)
                cell.number_format = "#,##0"
    
    def get_detailed_statistics(self):
        """获取详细统计信息（不修改文件）"""
        if not self.wb:
            if not self.load_workbook():
                return None
        
        statistics = {
            "total_sheets": len(self.wb.sheetnames),
            "sheets": [],
            "summary": {
                "total_data_rows": 0,
                "total_all_rows": 0,
                "sheets_with_data": 0
            }
        }
        
        for sheet_name in self.wb.sheetnames:
            ws = self.wb[sheet_name]
            all_rows = self._get_sheet_row_count(ws)
            data_rows = max(0, all_rows - 1)
            has_header = self._check_has_header(ws)
            
            sheet_stats = {
                "name": sheet_name,
                "all_rows": all_rows,
                "data_rows": data_rows,
                "has_header": has_header
            }
            
            statistics["sheets"].append(sheet_stats)
            statistics["summary"]["total_data_rows"] += data_rows
            statistics["summary"]["total_all_rows"] += all_rows
            
            if data_rows > 0:
                statistics["summary"]["sheets_with_data"] += 1
        
        return statistics
    
    def save_workbook(self, output_path=None):
        """保存工作簿"""
        if not output_path:
            output_path = self.file_path
        
        try:
            self.wb.save(output_path)
            print(f"💾 文件已保存: {output_path}")
            return True
        except Exception as e:
            print(f"✗ 保存文件失败: {e}")
            return False
    
    def close_workbook(self):
        """关闭工作簿"""
        if self.wb:
            self.wb.close()
            print("✓ 工作簿已关闭")

def create_excel_summary(file_path, output_path=None, show_preview=True):
    """
    快速创建Excel统计汇总
    
    参数:
    file_path: Excel文件路径
    output_path: 输出文件路径（None则覆盖原文件）
    show_preview: 是否显示预览统计信息
    """
    generator = ExcelStatsGenerator(file_path)
    
    try:
        # 加载工作簿
        if not generator.load_workbook():
            return False
        
        # 显示预览统计
        if show_preview:
            print("\n📋 预览统计信息:")
            stats = generator.get_detailed_statistics()
            if stats:
                for sheet in stats["sheets"]:
                    print(f"  📄 {sheet['name']}: {sheet['data_rows']} 数据行")
                print(f"  📊 总计: {stats['summary']['total_data_rows']} 数据行")
        
        # 创建汇总工作表
        print("\n🎯 正在创建统计汇总工作表...")
        if generator.create_summary_sheet():
            # 保存文件
            save_path = output_path if output_path else file_path
            if generator.save_workbook(save_path):
                print(f"\n✅ 统计汇总完成！")
                return True
        
        return False
    
    except Exception as e:
        print(f"\n❌ 发生错误: {e}")
        return False
    
    finally:
        generator.close_workbook()

def interactive_mode():
    """交互式模式"""
    print("📊 Excel工作表统计汇总工具")
    print("=" * 50)
    
    # 获取文件路径
    file_path = input("请输入Excel文件路径: ").strip()
    if not os.path.exists(file_path):
        print("❌ 文件不存在！")
        return
    
    # 选择输出方式
    output_choice = input("是否保存为新文件？(y/N): ").strip().lower()
    output_path = None
    if output_choice == 'y':
        output_path = input("请输入新文件路径: ").strip()
    
    # 执行统计
    success = create_excel_summary(file_path, output_path, show_preview=True)
    
    if success:
        print("\n🎉 操作成功完成！")
    else:
        print("\n⚠️ 操作失败")

# 示例用法
if __name__ == "__main__":
    # 方法1: 交互式使用
    #interactive_mode()
    
    create_excel_summary("D:\\BaiduNetdisk\\测试\\科学城机房门禁人员名单10.30(校验数据).xlsx")
    # 方法2: 快速使用（取消注释以下行）
    """
    # 基本用法 - 覆盖原文件
    create_excel_summary("example.xlsx")
    
    # 保存为新文件
    create_excel_summary("example.xlsx", "example_with_summary.xlsx")
    
    # 不显示预览
    create_excel_summary("example.xlsx", show_preview=False)
    """